from ast import Pass
from shutil import which
from turtle import update
import db_helper


def main():
    with db_helper.buildDbHelper(user='mysql',database='IntermediaryMedia') as db:
        updateProvinceSql=f" update Region set ParentCode='000000' , RankType=1 where code like '%0000'"
        # db.executeCommit(updateProvinceSql)
        sql="select * from Region where code like '%0000'; "
        provinceRecords = db.executeQuerySql(sql)
        for provinceRecord in provinceRecords:
            # print(record[0][0:3])
            provinceSql=f"select * from Region where code like'{provinceRecord[0][0:2]}%' and ParentCode <> '000000'"
            cityRecords = db.executeQuerySql(provinceSql)
            for cityRecord in cityRecords:
                # print(provinceRecord)
                updateCitySql=f'''update Region set ParentCode='{provinceRecord[0]}',RankType=2,Name='{"".join(str(cityRecord[1]).split())}' where code='{cityRecord[0]}' '''
                # db.executeCommit(updateCitySql)
                countySql = f''' select * from Region where code like '{cityRecord[0][0:4]}%' '''
                countyRecords= db.executeQuerySql(countySql)
                for countyRecord in countyRecords:
                    updateCountySql = f''' update Region set  ParentCode='{cityRecord[0]}',RankType=4,Name='{"".join(str(countyRecord[1]).split())}' where code='{countyRecord[0]}' '''
                    db.executeCommit(updateCountySql)
                    print(countyRecord)


    pass


def province():
    sql=''' select * from Region where Code like '%0000' '''
    with db_helper.buildDbHelper(user='mysql',database='IntermediaryMedia') as db:
        records= db.executeQuerySql(sql)
        for record in records:
            city(str(record[0]))
        pass

def city(provinceCode):
    sql=f''' select * from Region where Code like '{provinceCode[0:2]}%00' and rankType<>1 '''
    with db_helper.buildDbHelper(user='mysql',database='IntermediaryMedia') as db:
        updateSql = f''' update Region set parentCode='{provinceCode}' , rankType=2 where  Code like '{provinceCode[0:2]}%00' and rankType<>1 '''
        db.executeCommit(updateSql)
        records= db.executeQuerySql(sql)
        for record in records:
            county(str(record[0]))
        pass

def county(cityCode):
    # sql=f''' select * from Region where Code like '{cityCode[0:2]}%00' and rankType<>1 '''
    with db_helper.buildDbHelper(user='mysql',database='IntermediaryMedia') as db:
        updateSql = f''' update Region set parentCode='{cityCode}' , rankType=4 where  Code like '{cityCode[0:4]}%' and rankType<>1 and rankType<>2 '''
        db.executeCommit(updateSql)
        # records= db.executeQuerySql(sql)
        pass
    pass

def handle():
    sql = f''' select * from Region '''
    with db_helper.buildDbHelper(user='mysql',database='IntermediaryMedia') as db:
        records= db.executeQuerySql(sql)
        parentCode='*'
        for index,val in enumerate(records):
            if index != len(records)-1:
                if(str(val[0]).endswith('00')):
                    parentCode=str(val[0])
                    continue
                updateSql=f''' update Region set ParentCode='{parentCode}' ,Name='{"".join(str(val[1]).split())}' where code='{str(val[0])}' '''
                db.executeCommit(updateSql)
            pass
        pass
    pass

if __name__ == '__main__':
    handle()